"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import MySQLdb
import traceback
from msnp.logger import Logger
from msnp.persistence.mysql.media_accessors import SynchChangesAccessor, \
    MediaAccessor, NetSynchChangesAccessor
from msnp.persistence import FileAccessor
from msnp.persistence.mysql import DBErrorHandler
from msnp.model import MediaModel

DATABASE = ""
USER = ""
PWD = ""
SCHEMA = ""
PATH_SQL_FILE = "../db_sql/media_sql/"

def __openConnection():
    """Opens a connection to the media database with the global arguments.
    
    Returns:
        Returns a tuple of (Connection, Cursor) or the tuple (None, None) if no connection could be set up.
    """
    
    database = MySQLdb.connect(DATABASE, USER, PWD, charset="utf8", use_unicode=True)
    if database != None:
        cursor = database.cursor()
        return (database, cursor)
    return (None, None)


def __closeConnection(database):
    """Close the connection to the database given as an argument.
    
    Args:
        database: The Connection object representing the database where the connection should be closed.
    """
    if database != None:
        database.close()
    else:
        Logger.info('database is None while closing')


def initDatabaseCredentials(host, username, password, schema):
    """Sets the media database credentials.
    
    Sets the global variables DATABASE, USER, PWD and SCHEMA
    to the corresponding given arguments.
    
    Args:
        host: A String representing the host name of the database.
        username: A String representing the user name.
        password: A String representing the password of the user.
        schema: A String representing the schema name.
    """
    #this method is a "setter" for these global variables. python interprets this as local variables without the "global"-statement.
    global DATABASE
    global USER
    global PWD
    global SCHEMA
    DATABASE = host
    USER = username
    PWD = password
    SCHEMA = schema


def existsSchemaAndTables(host, user, password, schema):
    """Check whether there exists a media database with the given schema and the correct tables.
    
    Args:
        host: A String representing the host name of the database.
        username: A String representing the user name.
        password: A String representing the password of the user.
        schema: A String representing the schema name.
        
    Returns:
        True is returned, if there exists a media database with the given schema and the correct tables, False otherwise.
    """
    try:
        initDatabaseCredentials(host, user, password, schema)
        (database, cursor) = __openConnection()
        return MediaAccessor.existsSchemaAndTables(database, cursor, SCHEMA)
    except:
        Logger.info('exception checking for existance of schema and tables (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.CHECK, traceback.format_exc())
    finally:
        __closeConnection(database)
    return False

def initSchema():
    """Initializes the media database.
    
    Opens a connection to the database, drops all tables
    and creates them again (to ensure the tables have correct columns and are empty).
    Then, it closes the connection.
    
    Returns:
        True is returned, if the initialization was successful, False otherwise.
    """
    try:
        (database, cursor) = __openConnection()
        #delete tables and schema
        MediaAccessor.dropTablesAndSchema(database, cursor, SCHEMA)
        #insert tables and schema
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "schema.sql")
        executed = SynchChangesAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "synch_changes.sql")
        MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "genre.sql")
        executed = MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "collection.sql")
        executed = MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "media.sql")
        executed = MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "rating.sql")
        executed = MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "artist.sql")
        executed = MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "audio.sql")
        executed = MediaAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "net_synch_changes.sql")
        executed = NetSynchChangesAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "last_netsynch.sql")
        executed = NetSynchChangesAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        if executed == False:
            return False
        return True
    except:
        Logger.info('exception initializing media database (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INIT, traceback.format_exc())
        return False
    finally:
        __closeConnection(database) 
    return False
        
############
## SELECT ##
############

def selectSynchInsertChanges():
    """Selects all insert entries of the synch_changes table, i.e. entries with the value INSERT at the column "event".
    
    Returns:
        A list of SynchChanges objects or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                changes = SynchChangesAccessor.selectSynchInsertChanges(database, cursor, SCHEMA) 
                return changes
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting all synch insert changes (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectSynchUpdateChanges():
    """Selects all update entries of the synch_changes table, i.e. entries with the value UPDATE at the column "event".
    
    Returns:
        A list of SynchChanges objects or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                changes = SynchChangesAccessor.selectSynchUpdateChanges(database, cursor, SCHEMA) 
                return changes
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting all synch update changes (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectSynchChangesSize():
    """Retrieves the number of entries of the SynchChanges table.
    
    Returns:
        An integer number representing the number of entries of the SynchChanges table or 0 if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                size = SynchChangesAccessor.selectSynchChangesSize(database, cursor, SCHEMA)
                return size
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting synch change size')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return 0

def selectNetSynchMediaIdsProcessedSince(timestamp):
    """Selects all entries of the NetSynchChanges table that were processed after the given timestamp,
    i.e. where the value of the column "timestamp" is bigger than the given timestamp.
    
    Args:
        timestamp: A float value representing a certain timestamp.
        
    Returns:
        A list of NetSynchChanges objects is returned, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                changes = NetSynchChangesAccessor.selectNetSynchMediaIdsProcessedSince(database, cursor, SCHEMA, timestamp) 
                return changes
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting net synch changes for group since (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectNetSynchMediasFromPlayerSince(lastProcessedId):
    """Selects all entries of the NetSynchChanges table that were inserted because of changes in the player and with an id higher than the given one,
    i.e. where the value of the column "source" is PLAYER and the value of the column "id" is bigger than the given id.
    
    Args:
        lastProcessedId: An integer number representing the id of a certain entry in the NetSynchChanges table.
        
    Returns:
        A list of NetSynchChanges objects is returned, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                changes = NetSynchChangesAccessor.selectNetSynchMediasFromPlayerSince(database, cursor, SCHEMA, lastProcessedId) 
                return changes
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting net synch changes for group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectNetSynchMediasFromNetworkProcessedSince(lastProcessedId):
    """Selects all entries of the NetSynchChanges table that were inserted because of changes in the network and with an id higher than the given one,
    i.e. where the value of the column "source" is NETWORK and the value of the column "id" is bigger than the given id.
    
    Args:
        lastProcessedId: An integer number representing the id of a certain entry in the NetSynchChanges table.
        
    Returns:
        A list of NetSynchChanges objects is returned, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                changes = NetSynchChangesAccessor.selectNetSynchMediasFromNetworkProcessedSince(database, cursor, SCHEMA, lastProcessedId) 
                return changes
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting net synch changes from network since (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectLastUpdateProcessedTimestamp(groupname, ownername):
    """Selects the timestamp of the entry in the NetSynchChanges table that has the given group name and owner name.
    
    Args:
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
        
    Returns:
        A float containing the stored timestamp is returned, or -1 if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.selectLastUpdateProcessedTimestamp(database, cursor, SCHEMA, groupname, ownername) 
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting last update processed timestamp (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectLastNetSynchId():
    """Selects the highest value of the column "id" of the table NetSynchChanges.
    
    Returns:
        An integer containing the highest id is returned, or -1 if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.selectLastNetSynchId(database, cursor, SCHEMA) 
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting last netsynch id for username (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return - 1

def selectLastNetSynchIdForUserAndGroup(username, groupname, ownername):
    """Selects from the LastNetSynch table the value of the column "last_netsynch_id"
    that corresponds to the given user name, group name and owner name. 
    
    Args:
        username: A String representing the user name.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
    
    Returns:
        An integer containing the id is returned, or -1 if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.selectLastNetSynchIdForUserAndGroup(database, cursor, SCHEMA, username, groupname, ownername) 
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting last netsynch id for username (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectNearestNetSynchIdForTimestamp(timestamp):
    """Selects from the NetSynchChanges table the value of the column "id"
    where the value of the column "timestamp" is nearest to the given timestamp. 
    
    Args:
        timestamp: A float containing a certain timestamp.
    
    Returns:
        An integer containing the id is returned, or -1 if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.selectNearestNetSynchIdForTimestamp(database, cursor, SCHEMA, timestamp) 
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting nearest netsynch id for group and timestamp (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return -1

def selectNetSynchIdForGroupInitial(groupname, ownername):
    """Selects from the NetSynchChanges table the value of the column "id"
    that corresponds to the given group name and owner name and where the value of the column "source" is INITIAl.
    
    Args:
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
    
    Returns:
        An integer containing the id is returned, or -1 if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.selectNetSynchIdForGroupInitial(database, cursor, SCHEMA, groupname, ownername) 
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting netsynch id for INITIAL group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectArtistForName(artistName):
    """Selects from the media database the artist with the given name.
    
    All artists are selected and matched with the given name (not exact comparison).
    The first one, that is similar to the given name is returned.
    
    Args:
        artistName: A String representing the searched artist's name.
    
    Returns:
        An Artist object containing the searched artist or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                artists = MediaAccessor.selectArtistForName(database, cursor, SCHEMA, artistName)
                return artists
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting artist for name ' + artistName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectCollectionForNameAndArtist(collectionName, artistName):
    """Selects from the media database the collection with the given name and from the given artist.
    
    All audios are selected. Artist and collection of each audio are matched with the given ones (not exact comparison).
    The first collection, that is similar to the given values is returned.
    
    Args:
        collectionName: A Sring representing the searched collection.
        artistName: A String representing the name of the collection's artist.
    
    Returns:
        A Collection object containing the searched artist or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                collection = MediaAccessor.selectCollectionForNameAndArtist(database, cursor, SCHEMA, collectionName, artistName)
                return collection
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting collection for name ' + collectionName + ", artist " + artistName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectGenreForName(genreName):
    """Selects from the media database the genre with the given name.
    
    All genres are selected and matched with the given name (not exact comparison).
    The first one, that is similar to the given name is returned.
    
    Args:
        genreName: A String representing the searched genre's name.
    
    Returns:
        A Genre object containing the searched genre or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                genre = MediaAccessor.selectGenreForName(database, cursor, SCHEMA, genreName)
                return genre
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting genre for name ' + genreName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectAudiosForMediaIds(mediaIds, ratingForUser):
    """Selects from the media database the audios corresponding to the given ids.
    
    Args:
        mediaIds: A list of integers containing the searched ids.
        ratingForUser: A list of Strings containing user names for which the rating of the Audio object should be selected and added.
            If None, then all existing ratings for the Audio object are retrieved.
    
    Returns:
        A list of Audio objects containing the searched audios.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                audios = MediaAccessor.selectAudiosForMediaIds(database, cursor, SCHEMA, mediaIds, ratingForUser)
                return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting audios for mediaIds')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None


def selectAudiosForTitleArtistAndCollection(title, artistName, collectionName):
    """Selects from the media database the audios corresponding to the given title, artist name and collection name.
    
    All audios are selected and matched with the given values (not exact comparison).
    All audios, that are similar to the given values are returned.
    
    Args:
        title: A String containing the title of the searched audio.
        artistName: A String containing the artist name of the searched audio.
        collectionName: A String containing the collection name of the searched audio.
    
    Returns:
        A list of Audio objects containing the searched audios.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                audios = MediaAccessor.selectAudiosForTitleArtistAndCollection(database, cursor, SCHEMA, title, artistName, collectionName)
                return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting audios for title: ' + title + ", artist: " + artistName + ", collection: " + collectionName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectAudiosForArtistAndCollection(artistName, collectionName):
    """Selects from the media database the audios corresponding to the given artist name and collection name.
    
    All audios are selected and matched with the given values (not exact comparison).
    All audios, that are similar to the given values are returned.
    
    Args:
        artistName: A String containing the artist name of the searched audio.
        collectionName: A String containing the collection name of the searched audio.
    
    Returns:
        A list of Audio objects containing the searched audios.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                audios = MediaAccessor.selectAudiosForTitleArtistAndCollection(database, cursor, SCHEMA, None, artistName, collectionName)
                return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting audios for artist: ' + artistName + ", collection: " + collectionName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None
    

def selectAudiosForArtist(artistName):
    """Selects from the media database the audios corresponding to the given artist name.
    
    All audios are selected and matched with the given value (not exact comparison).
    All audios, that are similar to the given value, are returned.
    
    Args:
        artistName: A String containing the artist name of the searched audio.
    
    Returns:
        A list of Audio objects containing the searched audios.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                audios = MediaAccessor.selectAudiosForTitleArtistAndCollection(database, cursor, SCHEMA, None, artistName, None)
                return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting audios for artist: ' + artistName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectAudiosForGenre(genreName):
    """Selects from the media database the audios corresponding to the given genre name.
    
    All audios are selected and matched with the given value (not exact comparison).
    All audios, that are similar to the given value, are returned.
    
    Args:
        genreName: A String containing the genre name of the searched audio.
    
    Returns:
        A list of Audio objects containing the searched audios.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                audios = MediaAccessor.selectAudiosForGenre(database, cursor, SCHEMA, genreName)
                return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting audios for genre: ' + genreName)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectRatingForMediaIdAndUsername(mediaId, username):
    """Selects from the media database the rating of the given user for the given media.
    
    Args:
        mediaid: An integer containing the id of the media, for which the rating should be retrieved.
        username: A String containing the user name, for which the rating should be retrieved.
    
    Returns:
        A list of Rating objects.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                rating = MediaAccessor.selectRatingForMediaIdAndUsername(database, cursor, SCHEMA, mediaId, username)
                return rating
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting rating for mediaId: ' + str(mediaId) + ", username: " + username)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectAllAudios():
    """Selects from the media database all audios.
    
    Returns:
        A list of all Audio objects.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                audios = MediaAccessor.selectAllAudios(database, cursor, SCHEMA)
                return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception all audios')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None
    
    
############
## INSERT ##
############

def insertNetSynchChange(netSynchChange):
    """Inserts the given NetSynchChanges object into the NetSynchChanges table.
    
    Args:
        netSynchChange: The NetSynchChanges object to be inserted.
        
    Returns:
        True is returned if the insertion was successful, False otherwise.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.insertNetSynchChange(database, cursor, SCHEMA, netSynchChange)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting net synch change')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return False

def setLastNetSynchIdForUserAndGroup(username, groupname, ownername, id):
    """Updates or inserts in the LastNetSynch table the given id as last_netsynch_id for the given user name, group name and owner name.
    
    Args:
        username: A String representing the user name.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
        id: An integer representing the id to be inserted or updated.
        
    Returns:
        True is returned if the insertion or update was successful, False otherwise.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.setLastNetSynchIdForUserAndGroup(database, cursor, SCHEMA, username, groupname, ownername, id)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting last net synch id for username')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return False

def setNetSychnChangeRelevantToFalse():
    """Sets in the table NetSynchChanges the value of the column "relevant" to false for all existing entries.
    
    Returns:
        True is returned, if the update was successful, False otherwise.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return NetSynchChangesAccessor.setNetSychnChangeRelevantToFalse(database, cursor, SCHEMA)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception setting net synch changes to not relevant')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return False

def insertArtist(artist):
    """Inserts the given artist into the media database.
    
    Args:
        artist: An Artist object containing the artist to insert.
    
    Returns:
        The id of the inserted artist is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                insertId = MediaAccessor.insertArtist(database, cursor, SCHEMA, artist)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new artist')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return insertId

def insertCollection(collection):
    """Inserts the given collection into the media database.
    
    Args:
        collection: A Collection object containing the collection to insert.
    
    Returns:
        The id of the inserted collection is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                insertId = MediaAccessor.insertCollection(database, cursor, SCHEMA, collection)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new collection')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return insertId

def insertGenre(genre):
    """Inserts the given genre into the media database.
    
    Args:
        genre: A Genre object containing the genre to insert.
    
    Returns:
        The id of the inserted genre is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                insertId = MediaAccessor.insertGenre(database, cursor, SCHEMA, genre)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new genre')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return insertId

def insertRating(rating, mediaId):
    """Inserts the given rating for the given media id into the media database.
    
    Args:
        rating: A Rating object containing the rating to insert.
        mediaId: An integer representing the id of the media for which the rating should be inserted.
    
    Returns:
        The id of the inserted rating is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                insertId = MediaAccessor.insertRating(database, cursor, SCHEMA, rating, mediaId)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new rating')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return insertId

def insertAudio(audio):
    """Inserts the given audio into the media database.
    
    Args:
        audio: An Audio object containing the audio to insert.
    
    Returns:
        The id of the inserted audio is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                insertId = MediaAccessor.insertAudio(database, cursor, SCHEMA, audio)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new audio')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return insertId

def insertMedia(media):
    """Inserts the given media into the media database.
    
    Args:
        media: A Media object containing the media to insert.
    
    Returns:
        The id of the inserted media is returned if the insertion was successful, -1 otherwise.
    """
    insertId = -1
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                insertId = MediaAccessor.insertMedia(database, cursor, SCHEMA, media)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new media')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return insertId

############
## UPDATE ##
############

def updateArtist(oldArtist, newArtist):
    """Updates the given oldArtist stored in the media database with the values of the given newArtist.
    
    Args:
        oldArtist: An Artist object containing the valued of the old artist stored in the media database.
        newArtist: An Artist object containing the new values of the artist.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    updated = MediaModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                updated = MediaAccessor.updateArtist(database, cursor, SCHEMA, oldArtist, newArtist)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating artist')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return updated

def updateCollection(oldCollection, newCollection):
    """Updates the given oldCollection stored in the media database with the values of the given newCollection.
    
    Args:
        oldCollection: A Collection object containing the valued of the old collection stored in the media database.
        newCollection: A Collection object containing the new values of the collection.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    updated = MediaModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                updated = MediaAccessor.updateCollection(database, cursor, SCHEMA, oldCollection, newCollection)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating collection')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return updated

#TODO depricated
def updateGenre(oldGenre, newGenre):
    """Updates the given oldGenre stored in the media database with the values of the given newGenre.
    
    Args:
        oldGenre: A Genre object containing the valued of the old genre stored in the media database.
        newGenre: A Genre object containing the new values of the genre.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    updated = MediaModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                updated = MediaAccessor.updateGenre(database, cursor, SCHEMA, oldGenre, newGenre)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating genre')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return updated

def updateRating(oldRating, newRating):
    """Updates the given oldRating stored in the media database with the values of the given newRating.
    
    Args:
        oldRating: A Rating object containing the valued of the old rating stored in the media database.
        newRating: A Rating object containing the new values of the rating.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    updated = MediaModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                updated = MediaAccessor.updateRating(database, cursor, SCHEMA, oldRating, newRating)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating rating')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return updated

def updateMedia(oldMedia, newMedia):
    """Updates the given oldMedia stored in the media database with the values of the given newMedia.
    
    Args:
        oldMedia: A Media object containing the valued of the old media stored in the media database.
        newMedia: A Media object containing the new values of the media.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    updated = MediaModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                updated = MediaAccessor.updateMedia(database, cursor, SCHEMA, oldMedia, newMedia)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating media')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return updated

def updateAudio(oldAudio, newAudio):
    """Updates the given oldAudio stored in the media database with the values of the given newAudio.
    
    Args:
        oldAudio: An Audio object containing the valued of the old audio stored in the media database.
        newAudio: An Audio object containing the new values of the audio.
    
    Returns:
        It returns MediaModel.ReturnType.UPDATED if the update was successful,
            MediaModel.ReturnType.NOT_UPDATED is returned if the values stored in the database are the same as the new values,
            or MediaModel.ReturnType.ERROR if an error occurred.
    """
    updated = MediaModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                updated = MediaAccessor.updateAudio(database, cursor, SCHEMA, oldAudio, newAudio)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating audio')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return updated

############
## DELETE ##
############

def deleteSynchChanges():
    """Deletes all entries of the NetSynchChanges table.
    
    Returns:
        True, if the deletion was successful, False otherwise.
    """
    isDeleted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isDeleted = SynchChangesAccessor.deleteSynchChanges(database, cursor, SCHEMA)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception deleting synch changes older than')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isDeleted

def deleteMedia(mediaId):
    """Deletes from the media database the media with the given id.
    
    Args:
        mediaId: An integer representing the id of the media to delete.
    
    Returns:
        True if the deletion was successful, False otherwise.
    """
    isDeleted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isDeleted = MediaAccessor.deleteMedia(database, cursor, SCHEMA, mediaId)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception deleting media ' + str(mediaId))
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isDeleted

def deleteCollection(collectionId):
    """Deletes from the media database the collection with the given id.
    
    Args:
        collectionId: An integer representing the id of the collection to delete.
    
    Returns:
        True if the deletion was successful, False otherwise.
    """
    isDeleted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isDeleted = MediaAccessor.deleteCollection(database, cursor, SCHEMA, collectionId)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception deleting collection ' + str(collectionId))
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isDeleted
